package net.chery.update.tool;

import com.alibaba.excel.EasyExcel;
import com.zaxxer.hikari.HikariDataSource;
import net.chery.exec.BaseExec;
import org.springframework.boot.jdbc.DataSourceBuilder;

import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.function.Consumer;

/**
 * @author gai
 */
public class EditData extends BaseExec {

    public static final String URL = "jdbc:mysql://10.2.3.130:3307/vehicle?characterEncoding=utf-8&allowMultiQueries=true&serverTimezone=GMT%2B8";
    public static final String PASSWORD = "smc@z9w6";
    public static final String USER = "root";

    public static void main(String[] args) {
        EditData editData = new EditData();
        editData.initDataSource();
//        editData.doSelect();
        editData.doUpdate();
    }


    public void doSelect() {
        List<Map<String, Object>> res = select("select s.vin,\n" +
                "       s.offline_production_time,\n" +
                "       s.veh_model_name,\n" +
                "       s.is_match,\n" +
                "       s.pack_code_size,\n" +
                "       s.module_code_size,\n" +
                "       s.cell_code_size,\n" +
                "       s.pack_code_list,\n" +
                "       s.autofactory_code,\n" +
                "       s.autofactory_name\n" +
                "from sys_veh_produce s\n" +
                "where s.offline_production_time >= DATE_SUB(CURDATE(), INTERVAL 8 DAY)\n" +
                "  and s.offline_production_time < CURDATE()\n" +
                "order by s.offline_production_time");
        for (Map<String, Object> row : res) {
            Map<String, Object> map = selectMap("select p.manufactory_code,\n" +
                    "        p.manufactory_name\n" +
                    "from sys_battery_pack_entity p where p.vin=? limit 1", row.get("vin"));
            row.putAll(map);
        }
        writeExcel(res, "D://pro.xlsx");
    }

    public void writeExcel(List<Map<String, Object>> rows, String file) {
        if (!rows.isEmpty()) {
            // 写入Excel文件
            List<String> titles = new ArrayList<>(rows.get(0).keySet());
            List<List<Object>> datas = new ArrayList<>(rows.size());
            for (Map<String, Object> row : rows) {
                List<Object> data = new ArrayList<>(titles.size());
                for (String title : titles) {
                    data.add(row.get(title));
                }
                datas.add(data);
            }
            EasyExcel.write(file)
                    .head(titles.stream().map(List::of).toList())
                    .sheet("Sheet1")
                    .doWrite(datas);
        }
        System.out.println("数据为空");
    }

    public void doUpdate() {
        //修改销售信息上海的上报状态为已上报
//        String sql="update sys_veh_sale set sync_state_sh='1' where vin = ?";
        //修改生产信息上海的上报状态为已上报
//        String sql="update sys_veh_produce set sync_state_sh='0' where vin = ?";
        //查询生产信息
        String sql = "select * from sys_veh_produce where vin = ?";
        String file = "E:\\projects\\battery-etl\\src\\main\\java\\net\\chery\\update\\tool\\update";
        initDataSource();
        readFile(file, v -> {
            try (Connection connection = dataSource.getConnection();
                 PreparedStatement statement = connection.prepareStatement(sql)) {
                statement.setString(1, v);
                try (ResultSet rs = statement.executeQuery()) {
                    if (!rs.next()) {
                        System.out.println(v);
                    }
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        });
        dataSource.close();
    }

    private void readFile(String file, Consumer<String> consumer) {
        try (BufferedReader reader = new BufferedReader(new InputStreamReader(new FileInputStream(file)))) {
            String x = null;
            while ((x = reader.readLine()) != null) {
                x = x.trim();
                if (!x.isEmpty()) {
                    consumer.accept(x);
                }
            }
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    private void initDataSource() {
        dataSource = DataSourceBuilder.create()
                .type(HikariDataSource.class)
                .driverClassName("com.mysql.cj.jdbc.Driver")
                .url(URL)
                .username(USER)
                .password(PASSWORD)
                .build();
        dataSource.setConnectionTimeout(60000);
        dataSource.setMaximumPoolSize(20);
    }
}
